﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Utils;
using MySql.Data.MySqlClient;

namespace Data
{
    public class Comisiones
    {
        private Planes _hnd_planes = new Planes();

        private Planes HndPlanes
        {
            get { return _hnd_planes; }
        }

        private Entities.Comision CreateInstance(DataRow row)
        {
            return new Entities.Comision(
                row["id"].ToInt32(),
                row["nombre"].ToString(),
                row["ano_especialidad"].ToInt32(),
                row["id_plan"].ToInt32()
            );
        }

        private Entities.Collections.ComisionesCollection CreateCollection(DataTable table)
        {
            Entities.Collections.ComisionesCollection c = new Entities.Collections.ComisionesCollection();
            foreach (DataRow r in table.Rows) c.Add(CreateInstance(r));

            return c;
        }

        public Entities.Comision GetByID(int id)
        {
            string s = "SELECT id, nombre, ano_especialidad, id_plan FROM comisiones WHERE id = @id";
            List<MySqlParameter> l = new List<MySqlParameter>();
            l.Add(new MySqlParameter("@id", id));
            Query q = new Query(s, l);
            DataTable t = q.Execute();

            if (t.Rows.Count > 0) return CreateInstance(t.Rows[0]);
            else return null;
        }

        public Entities.Collections.ComisionesCollection GetByPlan(Entities.Plan plan)
        {
            string s = "SELECT id, nombre, ano_especialidad, id_plan FROM comisiones WHERE id_plan = @id_plan ORDER BY nombre";
            List<MySqlParameter> l = new List<MySqlParameter>();
            l.Add(new MySqlParameter("@id_plan", plan.ID));

            return CreateCollection(new Query(s, l).Execute());
        }

        public Entities.Collections.ComisionesCollection GetCollection()
        {
            string s = "SELECT id, nombre, ano_especialidad, id_plan FROM comisiones ORDER BY nombre";

            return CreateCollection(new Query(s, new List<MySqlParameter>()).Execute());
        }

        private void Insert(Entities.Comision instance)
        {
            if (instance.State != Commons.EntityState.New) return;

            string s = "INSERT INTO comisiones ( nombre, ano_especialidad, id_plan ) VALUES ( @nombre, @ano_especialidad, @id_plan )";
            List<MySqlParameter> l = new List<MySqlParameter>();
            l.Add(new MySqlParameter("@nombre", instance.Nombre));
            l.Add(new MySqlParameter("@ano_especialidad", instance.AnoEspecialidad));
            l.Add(new MySqlParameter("@id_plan", instance.IDPlan));
            new NonQuery(s, l).Execute();
        }

        private void Update(Entities.Comision instance)
        {
            if (instance.State != Commons.EntityState.Modified) return;

            string s = "UPDATE comisiones SET nombre = @nombre, ano_especialidad = @ano_especialidad, id_plan = @id_plan WHERE id = @id";
            List<MySqlParameter> l = new List<MySqlParameter>();
            l.Add(new MySqlParameter("@nombre", instance.Nombre));
            l.Add(new MySqlParameter("@ano_especialidad", instance.AnoEspecialidad));
            l.Add(new MySqlParameter("@id_plan", HndPlanes.GetByID(instance.IDPlan).ID));
            l.Add(new MySqlParameter("@id", instance.ID));
            new NonQuery(s, l).Execute();
        }

        public void Delete(Entities.Comision instance)
        {
            if (instance.State != Commons.EntityState.Deleted) return;

            string s = "DELETE FROM comisiones WHERE id = @id";
            List<MySqlParameter> l = new List<MySqlParameter>();
            l.Add(new MySqlParameter("@id", instance.ID));
            new NonQuery(s, l).Execute();
        }

        public void Save(Entities.Comision instance)
        {
            if (instance.State == Commons.EntityState.New) Insert(instance);
            else Update(instance);
        }
    }
}
